from tools.dbo import get_server_data_car, put_mysql_data, get_server_data, carried_sql

# 盒子与样本关联关系
def syn_sample_box(sql_server_conn, mysql_conn):
    print("===============================正在迁移样本盒子数据==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_box_sample")
    sample_sql = "select id,storeContainerId,storeContainerPositionX,storeContainerPositionY,isKeepPosition,2 from AP_SP_Sample where storeContainerId is not null and storeContainerId <> '' and storeContainerId <> 0"
    return_data = get_server_data_car(sql_server_conn, sample_sql)
    print(put_mysql_data(mysql_conn, "sample_id,box_id,position_row,position_column,keep_position,tenant_id", "eqm_box_sample", return_data))
    print("===============================样本盒子数据迁移完成==============================")

# 盒子迁移
# SELECT id,freezeBoxNo,freezeBoxNm,freezeBoxTypeId,freezeShelvesId,position,mark,isEffect,isDel from AP_Equ_FreezeBox
# SELECT id,box_code,box_name,box_type_id,shelf_id,position,remark,status,delete_flag from eqm_freeze_box
def syn_eqm_box(sql_server_conn, mysql_conn):
    print("===============================正在迁移盒子数据==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_freeze_box")
    return_data = get_server_data(sql_server_conn, "id,freezeBoxNo,ISNULL(freezeBoxNm,' '),freezeBoxTypeId,freezeShelvesId,position,mark,isEffect,isDel,isrtEmpId,isrtDt,updtEmpId,updtDt,2","1 = 1","AP_Equ_FreezeBox")
    print(put_mysql_data(mysql_conn, "id,box_code,box_name,box_type_id,shelf_id,position,remark,status,delete_flag,create_id,create_time,update_id,update_time,tenant_id", "eqm_freeze_box", return_data))
    print("===============================盒子数据迁移完成==============================")

# 架子迁移
# SELECT id,freezeShelvesNo,freezeShelvesNm,freezeShelvesTypeId,freezeAreaId,sortIndex,mark,isDel from AP_Equ_FreezeShelves
# SELECT id,shelf_code,shelf_name,shelf_type_id,layer_id,shelf_order,remark,delete_flag from eqm_shelf
def syn_eqm_shelf(sql_server_conn, mysql_conn):
    print("===============================正在迁移架子数据==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_shelf")
    return_data = get_server_data(sql_server_conn, "id,freezeShelvesNo,freezeShelvesNm,freezeShelvesTypeId,freezeAreaId,sortIndex,mark,isDel,isrtEmpId,isrtDt,updtEmpId,updtDt,2","1 = 1","AP_Equ_FreezeShelves")
    print(put_mysql_data(mysql_conn, "id,shelf_code,shelf_name,shelf_type_id,layer_id,shelf_order,remark,delete_flag,create_id,create_time,update_id,update_time,tenant_id", "eqm_shelf", return_data))
    print("===============================架子数据迁移完成==============================")

# 层级迁移
# SELECT freezeAreaNm,iceBoxId,sortIndex,mark,isDel from AP_Equ_FreezeArea
# SELECT layer_name,device_id,layer_order,remark,delete_flag from eqm_layer
def syn_eqm_layer(sql_server_conn, mysql_conn):
    print("===============================正在迁移层级数据==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_layer")
    return_data = get_server_data(sql_server_conn, "id,freezeAreaNm,iceBoxId,0,mark,isDel,isrtEmpId,isrtDt,updtEmpId,updtDt,2","1 = 1","AP_Equ_FreezeArea")
    print(put_mysql_data(mysql_conn, "id,layer_name,device_id,layer_order,remark,delete_flag,create_id,create_time,update_id,update_time,tenant_id", "eqm_layer", return_data))
    print("===============================层级数据迁移完成==============================")

# 房间信息迁移
def syn_room(sql_server_conn, mysql_conn):
    print("===============================正在迁移房间数据==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_room")
    return_data = get_server_data(sql_server_conn, "id,roomNm,0,mark,isDel,2","1=1","AP_Equ_Room")
    print(put_mysql_data(mysql_conn, "id,room_name,room_order,room_describe,delete_flag,tenant_id", "eqm_room", return_data))
    print("===============================房间数据迁移完成==============================")


# 设备型号迁移
def sys_device_model(sql_server_conn, mysql_conn):
    print("===============================正在迁移设备型号==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_model_configuration")
    return_data = get_server_data(sql_server_conn, "id,iceBoxModelNo,iceBoxModelNm,mark,2","1=1","AP_Equ_IceBoxModel")
    print(put_mysql_data(mysql_conn, "id,code,name,remark,tenant_id", "eqm_model_configuration", return_data))
    print("===============================设备型号迁移完成==============================")



# 冰冻架类型迁移
def sys_sheleves_type(sql_server_conn, mysql_conn):
    print("===============================正在迁移冻存架类型==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_shelf_type")
    return_data = get_server_data(sql_server_conn, "id,shelvesTypeNm,mark,[rowCount],columnCount,isnull(shelvesSort,2),isnull(layoutSort,1),disableCells,isDel,2","1=1","AP_Equ_ShelvesType")
    print(put_mysql_data(mysql_conn, "id,type_name,remark,row_num,column_num,category,layout,disable_cell,delete_flag,tenant_id","eqm_shelf_type", return_data))
    print("===============================正在迁移冻存架类型迁移完成==============================")

# 冰冻盒类型迁移
def sys_box_type(sql_server_conn, mysql_conn):
    print("===============================正在迁移冻存盒类型==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_freeze_box_type")
    return_data = get_server_data(sql_server_conn, "id,case boxSort when 0 then 1 else 2 end,boxTypeNm,mark,[rowCount],case rowCoordinateType when 'Letter' then 0 else 1 end,columnCount,case columnCoordinateType when 'Letter' then 0 else 1 end,maxCapacity,disableCells,isDel,2","1=1","AP_Equ_BoxType")
    print(put_mysql_data(mysql_conn, "id,category,type_name,remark,row_num,row_type,column_num,column_type,max_size,disable_cell,delete_flag,tenant_id","eqm_freeze_box_type", return_data))
    # 更新冰冻盒容量数据
    update_maxsize_sql = "update eqm_freeze_box_type set max_size=row_num*column_num where category=1"
    carried_sql(mysql_conn,update_maxsize_sql)
    print("===============================冻存盒类型迁移完成==============================")


# 设备信息迁移
# select id,iceBoxNo,iceBoxNm,refrigeratorBrand,refrigeratorMode,iceBoxTypeNo,roomId,departId,isDel,sortIndex,mark from AP_Equ_IceBox
# select id,device_code,device_name,device_brand,eqm_model_id,eqm_type_id,room_id,depart_id,delete_flag,device_order,remark from eqm_device
def syn_device(sql_server_conn, mysql_conn):
    print("===============================正在迁移设备数据==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_device")
    iceBoxTypeNo="case iceBoxTypeNo when '01' then 1 when '03' then 2 end"
    return_data = get_server_data(sql_server_conn, "id,iceBoxNo,iceBoxNm,refrigeratorBrand,refrigeratorMode,iceBoxTypeNo,roomId,departId+99,0,mark,isrtEmpId,isrtDt,updtEmpId,updtDt,2","isDel = 0","AP_Equ_IceBox")
    print(put_mysql_data(mysql_conn, "id,device_code,device_name,device_brand,eqm_model_id,eqm_type_id,room_id,depart_id,device_order,remark,create_id,create_time,update_id,update_time,tenant_id", "eqm_device", return_data))
    print("===============================设备数据迁移完成==============================")

# 更新盒子抽屉关联关系
def update_eqm_freeze_box_drawer_rel(sql_server_conn, mysql_conn):
    print("===============================正在更新盒子与抽屉的关系==============================")
    update_eqm_freeze_box_drawer_rel_sql ="""
    update eqm_freeze_box box
    inner join 
    (
        select tmp.id box_id,drawer.id drawer_id from eqm_drawer drawer
        left join (
        select id,shelf_id,concat('抽屉',SUBSTRING_INDEX(position,',',1)) drawer_name from eqm_freeze_box where delete_flag <> 1
        ) tmp on drawer.shelf_id=tmp.shelf_id and drawer.drawer_name = tmp.drawer_name
    ) res on box.id = res.box_id
    set box.drawer_id = res.drawer_id
    """
    carried_sql(mysql_conn,update_eqm_freeze_box_drawer_rel_sql)
    print("===============================盒子与抽屉关系更新完成==============================")

# 生成抽屉数据
def generate_drawer(sql_server_conn, mysql_conn):
    print("===============================正在生成抽屉数据==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_drawer")
    select_eqm_self_drawer_sql = """
    select es.id,est.row_num drawer from eqm_shelf es
    LEFT JOIN eqm_shelf_type est ON es.shelf_type_id = est.id
    where category=2
    """
    return_data = get_server_data_car(mysql_conn,select_eqm_self_drawer_sql)
    res = []
    for item in return_data:
        for i in range(item[1]):
            res.append([item[0],"抽屉"+str(i+1),2])
    print(put_mysql_data(mysql_conn,"shelf_id,drawer_name,tenant_id","eqm_drawer",res))
    print("===============================抽屉数据生成完成==============================")


# 设备类型
def sys_device_type(sql_server_conn, mysql_conn):
    print("===============================正在迁移设备类型==============================")
    carried_sql(mysql_conn,"TRUNCATE eqm_type_configuration")
    carried_sql(mysql_conn,"INSERT INTO eqm_type_configuration(`id`, `name`, `code`, `remark`, `auto_flag`, `img_type`,`tenant_id`) VALUES (1, '冰箱', 'bingxiang', NULL, 0, 0,2);")
    carried_sql(mysql_conn,"INSERT INTO eqm_type_configuration(`id`, `name`, `code`, `remark`, `auto_flag`, `img_type`,`tenant_id`) VALUES (2, '液氮罐', 'yedanguan', NULL, 0, 1,2);")
    carried_sql(mysql_conn,"INSERT INTO eqm_type_configuration(`id`, `name`, `code`, `remark`, `auto_flag`, `img_type`,`tenant_id`) VALUES (3, '玻片柜', 'popinagui', NULL, 0, 2,2);")
    carried_sql(mysql_conn,"INSERT INTO eqm_type_configuration(`id`, `name`, `code`, `remark`, `auto_flag`, `img_type`,`tenant_id`) VALUES (4, '蜡块柜', 'lajvgui', NULL, 0, 3,2);")
    print("===============================设备类型迁移完成==============================")
